<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="renderer" content="webkit">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
    <title>Excel转SQL生成器</title>
    <link href="https://mirror.fe80.cn/twitter-bootstrap/5.3.2/css/bootstrap.min.css" rel="stylesheet">
    <style>
        .upload-area {
            border: 2px dashed #007bff;
            border-radius: 10px;
            padding: 2rem;
            text-align: center;
            transition: all 0.3s;
            background-color: #f8f9fa;
        }

        .upload-area:hover {
            background-color: #e9ecef;
            border-color: #0056b3;
        }

        #sql-output {
            font-family: 'Courier New', Courier, monospace;
            white-space: pre-wrap;
            background-color: #f8f9fa;
            padding: 1rem;
            border-radius: 5px;
            max-height: 500px;
            overflow-y: auto;
        }

        .hidden {
            display: none;
        }

        .highlight {
            color: #d63384;
        }
    </style>
</head>
<body>
    <div class="container py-5">
        <h1 class="mb-4 text-center">Excel转SQL生成器</h1>
        
        <div class="card shadow mb-4">
            <div class="card-body">
                <div class="upload-area" id="drop-zone">
                    <p>将Excel文件拖放到此处或点击选择文件</p>
                    <input type="file" id="file-input" class="form-control" accept=".xlsx, .xls">
                </div>
                <div class="mt-3 text-center">
                    <small class="text-muted">支持.xls和.xlsx格式文件，第一行为字段标题</small>
                </div>
            </div>
        </div>

        <div id="result-section" class="hidden mt-5">
            <div class="d-flex justify-content-between align-items-center mb-3">
                <h4>生成的SQL语句</h4>
                <button id="copy-btn" class="btn btn-outline-success">复制到剪贴板</button>
            </div>
            <div id="sql-output" class="p-3 border"></div>
        </div>
    </div>

    <script src="https://mirror.fe80.cn/xlsx/0.18.5/xlsx.full.min.js"></script>
    <script>
        const fileInput = document.getElementById('file-input');
        const dropZone = document.getElementById('drop-zone');
        const sqlOutput = document.getElementById('sql-output');
        const resultSection = document.getElementById('result-section');
        const copyBtn = document.getElementById('copy-btn');

        // 处理文件选择
        fileInput.addEventListener('change', handleFile);
        
        // 拖放功能处理
        dropZone.addEventListener('dragover', (e) => {
            e.preventDefault();
            dropZone.classList.add('bg-primary', 'text-white');
        });

        dropZone.addEventListener('dragleave', () => {
            dropZone.classList.remove('bg-primary', 'text-white');
        });

        dropZone.addEventListener('drop', (e) => {
            e.preventDefault();
            dropZone.classList.remove('bg-primary', 'text-white');
            const file = e.dataTransfer.files[0];
            handleFile({ target: { files: [file] } });
        });

        // 处理文件
        function handleFile(e) {
            const file = e.target.files[0];
            if (!file) return;

            const reader = new FileReader();
            reader.onload = (e) => {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, { type: 'array' });
                const worksheet = workbook.Sheets[workbook.SheetNames[0]];
                const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

                if (jsonData.length < 2) {
                    alert('文件内容不足，至少需要标题行和数据行');
                    return;
                }

                const [headers, ...rows] = jsonData;
                const sql = generateSQL(headers, rows);
                sqlOutput.textContent = sql;
                resultSection.classList.remove('hidden');
            };
            reader.readAsArrayBuffer(file);
        }

        // 生成SQL
        function generateSQL(headers, rows) {
            const tableName = 'your_table_name'; // 修改为实际表名
            const escapedHeaders = headers.map(h => `\`${h}\``).join(', ');
            
            return rows.map(row => {
                const values = row.map(value => {
                    if (value === null || value === undefined) return 'NULL';
                    if (typeof value === 'number') return value;
                    return `'${value.toString().replace(/'/g, "''")}'`;
                }).join(', ');
                
                return `INSERT INTO ${tableName} (${escapedHeaders}) VALUES (${values});`;
            }).join('\n');
        }

        // 复制功能
        copyBtn.addEventListener('click', () => {
            navigator.clipboard.writeText(sqlOutput.textContent)
                .then(() => {
                    copyBtn.textContent = '复制成功！';
                    setTimeout(() => {
                        copyBtn.textContent = '复制到剪贴板';
                    }, 2000);
                })
                .catch(err => {
                    console.error('复制失败:', err);
                });
        });
    </script>
</body>
</html>